﻿using System;
using System.Collections.Generic;
using System.Text;
using DDBuildTools.DataAccess.Components;
using System.Data.OleDb;
using System.Data;
using System.Data.Common;

namespace DDBuildTools.DataAccess
{

    /// <summary>
    /// Access数据库
    /// </summary>
    public class AccessDB : IDataBase
    {

        /// <summary>
        /// 获得表
        /// </summary>
        /// <param name="ConnStr"></param>
        /// <param name="DataBaseCode"></param>
        /// <returns></returns>
        public List<TableEntity> GetDataBaseTable(string ConnStr,out string DataBaseCode)
        {
            DataBaseCode = GetDBname(ConnStr);
            List<TableEntity> TableList = new List<TableEntity>();
            
            OleDbConnection AccessConn = new OleDbConnection(ConnStr);
            AccessConn.Open();
            DataTable dt = AccessConn.GetSchema("Tables");

            foreach (DataRow var in dt.Select("TABLE_TYPE = 'TABLE'", "Table_Name asc"))
            {
                TableEntity Tb = new TableEntity();
                Tb.DataBaseCode = "";
                Tb.TableCode = var["Table_Name"].ToString();
                Tb.TableName = "";
                List<TableColume> TbColume = new List<TableColume>();

                DataTable dtColumns = new DataTable();
                dtColumns.Columns.Add("表名", System.Type.GetType("System.String"));
                dtColumns.Columns.Add("字段名", System.Type.GetType("System.String"));
                dtColumns.Columns.Add("类型", System.Type.GetType("System.String"));
                dtColumns.Columns.Add("长度", System.Type.GetType("System.String"));
                dtColumns.Columns.Add("小数位数", System.Type.GetType("System.String"));
                dtColumns.Columns.Add("字段说明", System.Type.GetType("System.String"));
                dtColumns.Columns.Add("默认值", System.Type.GetType("System.String"));
                dtColumns.Columns.Add("标识", System.Type.GetType("System.Int32"));
                dtColumns.Columns.Add("主键", System.Type.GetType("System.Int32"));
                dtColumns.Columns.Add("允许空", System.Type.GetType("System.Int32"));

                //获取备注
                DataTable Columns_Memo = AccessConn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, var["Table_Name"].ToString() });
                foreach (DataRow varTemp in Columns_Memo.Select("", "Ordinal_Position asc"))
                {
                    DataRow row = dtColumns.NewRow();
                    row["表名"] = varTemp["table_Name"];
                    row["字段名"] = varTemp["column_name"];
                    row["字段说明"] = varTemp["description"];
                    row["默认值"] = varTemp["column_default"];

                    dtColumns.Rows.Add(row);
                }

                //获取字段类型
                OleDbCommand cmd = new OleDbCommand(string.Format("select top 1 * from {0}", var["Table_Name"].ToString()), AccessConn);
                cmd.CommandType = CommandType.Text;
                OleDbDataReader dr = cmd.ExecuteReader(CommandBehavior.KeyInfo);
                Columns_Memo = dr.GetSchemaTable();

                for (int i = 0; i < Columns_Memo.Rows.Count; i++)
                {                    
                    (dtColumns.Select("字段名='" + Columns_Memo.Rows[i]["ColumnName"] + "'"))[0]["长度"] = Columns_Memo.Rows[i]["ColumnSize"].ToString();
                    (dtColumns.Select("字段名='" + Columns_Memo.Rows[i]["ColumnName"] + "'"))[0]["小数位数"] = Columns_Memo.Rows[i]["NumericScale"].ToString();
                    (dtColumns.Select("字段名='" + Columns_Memo.Rows[i]["ColumnName"] + "'"))[0]["标识"] = (Columns_Memo.Rows[i]["IsAutoIncrement"].ToString() == "True" ? 1 : 0);
                    (dtColumns.Select("字段名='" + Columns_Memo.Rows[i]["ColumnName"] + "'"))[0]["允许空"] = (Columns_Memo.Rows[i]["AllowDBNull"].ToString() == "True" ? 1 : 0);
                    (dtColumns.Select("字段名='" + Columns_Memo.Rows[i]["ColumnName"] + "'"))[0]["主键"] = (Columns_Memo.Rows[i]["IsKey"].ToString() == "True" ? 1 : 0);
                    (dtColumns.Select("字段名='" + Columns_Memo.Rows[i]["ColumnName"] + "'"))[0]["类型"] = DataTypeToSQLDataType(Columns_Memo.Rows[i]["ProviderType"].ToString());

                }

                for (int i = 0; i < dtColumns.Rows.Count; i++)
                {
                    TableColume tl = new TableColume();
                    tl.Colume_Code = dtColumns.Rows[i]["字段名"].ToString();
                    tl.Colume_DefaultValue = dtColumns.Rows[i]["默认值"].ToString();
                    tl.Colume_Name = dtColumns.Rows[i]["字段说明"].ToString();
                    tl.Colume_Type = dtColumns.Rows[i]["类型"].ToString();
                    tl.Colume_Length = dtColumns.Rows[i]["长度"].ToString();
                    tl.Colume_Scale = dtColumns.Rows[i]["小数位数"].ToString();
                    tl.Colume_IsNullAble = Convert.ToBoolean(dtColumns.Rows[i]["允许空"]);
                    tl.Colume_IsKey = Convert.ToBoolean(dtColumns.Rows[i]["主键"]);
                    tl.Colume_Identity = Convert.ToBoolean(dtColumns.Rows[i]["标识"]);
                    TbColume.Add(tl);                    
                }
                Tb.TableColume = TbColume;
                TableList.Add(Tb);
            }
            AccessConn.Close();
            AccessConn.Dispose();
            return TableList;
        }

        private string DataTypeToSQLDataType(string DataTypeName)
        {
            string rString = "none";
            switch (DataTypeName)
            { 
                case "2":
                    rString = "Int16";
                    break;
                case "3":
                    rString = "Int32";
                    break;
                case "4":
                    rString = "Single";
                    break;
                case "5":
                    rString = "Double";
                    break;
                case "6":
                    rString = "Decimal";
                    break;
                case "7":
                    rString = "DateTime";
                    break;
                case "11":
                    rString = "Boolean";
                    break;
                case "17":
                    rString = "Byte";
                    break;
                case "131":
                    rString = "Decimal";
                    break;
                case "202":
                    rString = "Text";
                    break;
                case "203":
                    rString = "Memo";
                    break;
                case "205":
                    rString = "OLE";
                    break;
                default:
                    rString = "None";
                    break;
            }
            return rString;
        }

        private string GetDBname(string Conn)
        {            
            OleDbConnectionStringBuilder Db = new OleDbConnectionStringBuilder();
            Db.ConnectionString = Conn;            
            return System.IO.Path.GetFileNameWithoutExtension(Db.DataSource);            
        }
    }
}
